package com.trkj.dao;

import com.trkj.entity.DepartmentTransfer;
import com.trkj.entity.Dept;
import com.trkj.entity.Employee;
import com.trkj.entity.EmployeeMovement;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import javax.sql.DataSource;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

@Repository
public class EmpDao extends JdbcDaoSupport {
    public EmpDao(DataSource druidDataSource){
        setDataSource(druidDataSource);
    }
    public int addEmployee(Employee emp){
        String sql1="insert into Employee(employee_id,name,gender,birth_date,hire_date,status,department_id) values(?,?,?,?,?,?,?)";
        int count=this.getJdbcTemplate().update(sql1,emp.getEmployee_id(),emp.getName(),emp.getGender(),emp.getBirth_date(),emp.getHire_date(),emp.getStatus(),emp.getDepartment_id());
        return count;
    }
    public int updateEmployee(Employee emp){
        String sql="update Employee set status=? where employee_id=?";
        int count=this.getJdbcTemplate().update(sql,emp.getStatus(),emp.getEmployee_id());
        return count;
    }
    public int updateEmployee2(Employee emp){
        String sql="update Employee set department_id=? where employee_id=?";
        int count=this.getJdbcTemplate().update(sql,emp.getDepartment_id(),emp.getEmployee_id());
        return count;
    }
    public int addDepartmentTransfer(DepartmentTransfer dept){
        String sql="insert into DepartmentTransfer(transfer_id,old_department_id,new_department_id,employee_id,transfer_date,transfer_reason) values (?,?,?,?,?,?)";
        int count=this.getJdbcTemplate().update(sql,dept.getTeansfer_id(),dept.getOld_department_id(),dept.getNew_department_id(),dept.getEmployee_id(),dept.getTransfer_date(),dept.getTransfer_reason());
        return count;
    }
    public int addEmployeeMovement(EmployeeMovement emp){
        String sql="insert into EmployeeMovement(movement_id,employee_id,movement_type,movement_date,remarks) values(?,?,?,?,?);";
        int count =this.getJdbcTemplate().update(sql,emp.getMovement_id(),emp.getEmployee_id(),emp.getMovement_type(),emp.getMovement_date(),emp.getRemarks());
        return count;
    }
    public List<Map<String,Object>> selectYG(int id){
        String sql="SELECT employee_id, movement_type AS activity_type, movement_date AS activity_date, remarks AS activity_remark\n" +
                "FROM EmployeeMovement\n" +
                "WHERE employee_id = ?\n" +
                "\n" +
                "UNION ALL\n" +
                "\n" +
                "SELECT employee_id, 'Department Transfer' AS activity_type, transfer_date AS activity_date, transfer_reason AS activity_remark\n" +
                "FROM DepartmentTransfer\n" +
                "WHERE employee_id = ?\n" +
                "\n" +
                "ORDER BY activity_date DESC;";
        List<Map<String,Object>> list=this.getJdbcTemplate().queryForList(sql,id,id);
        return list;
    }
    public List<Map<String, Object>> countEmployeesByYear() {
        String sql = "SELECT YEAR(hire_date) AS hire_year, COUNT(*) AS employee_count " +
                "FROM Employee " +
                "GROUP BY YEAR(hire_date) " +
                "ORDER BY hire_year";

        List<Map<String, Object>> resultList = this.getJdbcTemplate().queryForList(sql);
        return resultList;
    }
    public List<Map<String, Object>> countEmployeesByDepartment() {
        String sql = "SELECT department_id, COUNT(*) AS employee_count " +
                "FROM Employee " +
                "GROUP BY department_id";

        List<Map<String, Object>> resultList = this.getJdbcTemplate().queryForList(sql);
        return resultList;
    }
    public int insertDept(Dept dept){
        String sql="insert into Department(department_id,department_name) values(?,?)";
        int count=this.getJdbcTemplate().update(sql,dept.getDeptno(),dept.getDname());
        return count;
    }
    public int deleteYG(int id){
        String sql="delete from employee where department_id=?";
        int count=this.getJdbcTemplate().update(sql,id);
        String sql2="DELETE FROM Department WHERE department_id = ?";
        int count2=this.getJdbcTemplate().update(sql,id);
        return count2;
    }
    public int updateEmployee3(){
        String sql="UPDATE Employee \n" +
                "SET salary = \n" +
                "    CASE\n" +
                "        WHEN salary < (SELECT AVG(salary) FROM (SELECT * FROM Employee) AS e) THEN salary * 1.2\n" +
                "        WHEN salary = (SELECT MAX(salary) FROM (SELECT * FROM Employee) AS e) THEN salary * 0.5\n" +
                "        ELSE salary\n" +
                "    END;\n";
        int count=this.getJdbcTemplate().update(sql);
        return count;
    }

}
